package news.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import news.entity.News;
import news.entity.Types;
import news.entity.Users;
import news.utils.DBUtils;

public class NewsDAO {
	/**
	 * 通过新闻类型id查询所有的新闻
	 * @param tid
	 * @return
	 */
	public List<News> listNews( int tid ) {
		List<News> listNews =null;
		String sql = "select n.newsid,u.userid,u.username,t.typesid,t.typesname,t.remark,n.title,n.newcontent,n.recommended,n.click,n.releasetime,n.keywords from userinfo u join newsinfo n on u.userid=n.userid join newstypeinfo t on t.typesid=n.typesid";
		if ( tid != 0 ) {
			sql += " where t.typesid=" + tid; 
		}
		
		Connection conn = DBUtils.getConn();
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			listNews = new ArrayList<>();
			while ( rs.next() ) {
				News news = new News();
				news.setNewsid(rs.getInt(1));
				news.setTitle(rs.getString(7));
				news.setContent(rs.getString(8));
				news.setRecommended(rs.getInt(9));
				news.setClick(rs.getInt(10));
				news.setReleasetime(rs.getDate(11));
				news.setKeywords(rs.getString(12));
				
				Users user = new Users();
				user.setId(rs.getInt(2));
				user.setName(rs.getString(3));
				
				Types type = new Types();
				type.setTypeid(rs.getInt(4));
				type.setTitle(rs.getString(5));
				type.setRemark(rs.getString(6));
				
				news.setUser(user);
				news.setType(type);
				
				listNews.add(news);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return listNews;
	}

	/**
	 * 发布新闻
	 * @param news
	 * @return
	 */
	public boolean relaseNews(News news) {
		boolean flag = false;
		String sql = "insert into newsinfo(typesid,userid,title,newcontent,recommended,keywords) values(?,?,?,?,?,?)";
		Connection conn = DBUtils.getConn();
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, news.getType().getTypeid());
			pst.setInt(2, news.getUser().getId());
			pst.setString(3, news.getTitle());
			pst.setString(4, news.getContent());
			pst.setInt(5, news.getRecommended());
			pst.setString(6, news.getKeywords());
			flag = pst.executeUpdate() > 0 ? true : false ;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
}
